const db = require('../config/mysql');
const dayjs = require('dayjs');

class InventoryinModel {
    constructor() {
        this.db = db;
    }
    // 入库列表查询
    async getInventoryinList(params) {

                // 确保参数转换为正确类型并设置默认值
        const page = parseInt(params.page) || 1;
        const page_size = parseInt(params.page_size) || 10;
        const code = params.code || '';
        const procurement = params.procurement || '';
        const stall_id = params.stall_id || '';
        const in_type = params.in_type || '';
        const status = params.status || '';
        // 格式化 in_date：若前端传了值，统一转成 YYYY-MM-DD
        const in_date = params.in_date||'';

        // 计算偏移量，确保页码有效
        const offset = Math.max(0, (page - 1) * page_size);

        // 分页查询搜索编号入库时间供应商
        let sql = 'SELECT in_id,code,in_type,order_id,in_date,in_amount,status,deliverer_name,deliverer_phone,receiver_uid,house_uid,notes,in_doc_pic,created_at,updated_at,finance_uid FROM inventoryin WHERE 1=1';
        const queryParams = [];

        // 动态添加搜索条件，避免不必要的通配符搜索
        if (code) {
            sql += ' AND code LIKE ?';
            queryParams.push('%' + code + '%');
        }
        if (procurement) {
            // 当传入order_code时，先查询goods_order表获取对应的order_id
            const orderSql = 'SELECT order_id FROM inventoryoutitem WHERE code = ?';
            const [orderResult] = await this.db.query(orderSql, [procurement]);
            if (orderResult && orderResult.length > 0) {
                const foundOrderId = orderResult[0].in_id;
                sql += ' AND in_id = ?';
                queryParams.push(foundOrderId);
            } else {
                // 如果找不到对应的订单，添加一个条件让结果为空
                sql += ' AND 1=0';
            }
        }
        if (stall_id) {
            sql += ' AND stall_id = ?';
            queryParams.push(stall_id);
        }
        if (in_type) {
            sql += ' AND in_type = ?';
            queryParams.push(in_type);
        }
        if (status) {
            sql += ' AND status = ?';
            queryParams.push(status);
        }
        // 添加排序和分页（移除对不存在的id列的引用）
        sql += ' ORDER BY in_date DESC LIMIT ? OFFSET ?';
        queryParams.push(page_size, offset);

        try {
            const [result] = await this.db.query(sql, queryParams);
            // 计算总记录数
            const countSql = 'SELECT COUNT(*) AS total FROM inventoryin WHERE 1=1';
            const [countResult] = await this.db.query(countSql, queryParams);
            const total = countResult[0].total;
            // 获取到刚刚查询出的所有记录的in_id，确保result不为null或undefined
            const in_ids = result && Array.isArray(result) ? result.map(item => item.in_id) : [];
            let inDetailResult = [];
            // 再查询入库子表中in_id在in_ids中的记录，先检查数组是否为空
            if (in_ids.length > 0) {
                const inDetailSql = 'SELECT * FROM inventoryinitem WHERE in_id IN (?)';
                [inDetailResult] = await this.db.query(inDetailSql, [in_ids]);
            }
            // 拿出所有子表记录中的supplier_id
            const supplier_ids = [...new Set(inDetailResult.map(item => item.supplier_id).filter(Boolean))];
            let supplierResult = [];
            if (supplier_ids.length) {
                const supsql = 'SELECT supplier_id, supplier_name FROM supplier WHERE supplier_id IN (?)';
                [supplierResult] = await this.db.query(supsql, [supplier_ids]);
            }
            // 再搜索采购订单中order_id在order_ids中的记录
            const order_ids = result.map(item => item.order_id).filter(Boolean);
            let orderResult = [];
            if (order_ids.length) {
                const orderSql = 'SELECT * FROM goods_order WHERE order_id IN (?)';
                [orderResult] = await this.db.query(orderSql, [order_ids]);
            }
            
            // 将主表、子表、订单数据组合成统一对象
            const combined = result.map(master => {
                // 找到对应子表记录
                const details = inDetailResult.filter(d => d.in_id === master.in_id);
                // 找到对应订单记录
                const order = orderResult.find(o => o.order_id === master.order_id) || {};
                // 找到对应供应商记录（根据子表中的supplier_id）
                const supplier = supplierResult.find(s => details.some(d => d.supplier_id === s.supplier_id)) || {};
                return {
                    ...master,          // 主表字段
                    details,            // 子表数组
                    order,              // 订单对象（可能为空）
                    supplier            // 供应商对象（含supplier_name，可能为空）
                };
            });
            return {
                data: combined,      // 合并后的完整数据
                total,
                page,
                page_size,
            };



        } catch (error) {
            console.error('查询库存记录失败:', error);
            throw error;
        }
    }

    // 根据采购订单的id获取采购订单详情
    async GetGoodsOrderDetail(order_id) {
        // 构建SQL语句，使用占位符避免SQL注入
        console.log('订单ID参数:', order_id);
        const sql = 'SELECT * FROM inventoryinitem WHERE in_id=?';
        console.log('SQL查询语句:', sql, order_id);

        try {
            // 第一步：查询库存子表中与订单相关的记录
            const [inventoryResults] = await this.db.query(sql, [order_id]);
            console.log('查询库存子表结果:', inventoryResults);

            // 如果没有找到记录，直接返回空数组
            if (inventoryResults.length === 0) {
                return [];
            }

            // 第二步：从结果中提取所需的ID列表
            const goods_ids = [...new Set(inventoryResults.map(item => item.goods_id).filter(Boolean))];
            const house_ids = [...new Set(inventoryResults.map(item => item.house_id).filter(Boolean))];
            const area_ids = [...new Set(inventoryResults.map(item => item.area_id).filter(Boolean))];
            const supplier_ids = [...new Set(inventoryResults.map(item => item.supplier_id).filter(Boolean))];
            // 提取in_id用于查询inventoryin表
            const in_id = [...new Set(inventoryResults.map(item => item.in_id).filter(Boolean))];

            console.log('提取的商品ID列表:', goods_ids);
            console.log('提取的仓库ID列表:', house_ids);
            console.log('提取的库区ID列表:', area_ids);
            console.log('提取的供应商ID列表:', supplier_ids);
            console.log('提取的入库ID列表:', in_id);

            // 第三步：并行查询相关信息（提升性能）
            let goodsResult = [];
            let houseResult = [];
            let areaResult = [];
            let supplierResult = [];
            let inventoryInResult = [];

            // 查询商品信息
            if (goods_ids.length > 0) {
                const goodsSql = 'SELECT * FROM goods WHERE goods_id IN (?)';
                const [goodsData] = await this.db.query(goodsSql, [goods_ids]);
                goodsResult = goodsData;
                console.log('查询商品表结果:', goodsResult);
            }

            // 查询仓库信息
            if (house_ids.length > 0) {
                const houseSql = 'SELECT * FROM house WHERE house_id IN (?)';
                const [houseData] = await this.db.query(houseSql, [house_ids]);
                houseResult = houseData;
                console.log('查询仓库表结果:', houseResult);
            }

            // 查询库区信息
            if (area_ids.length > 0) {
                const areaSql = 'SELECT * FROM house_area WHERE area_id IN (?)';
                const [areaData] = await this.db.query(areaSql, [area_ids]);
                areaResult = areaData;
                console.log('查询库区表结果:', areaResult);
            }

            // 查询供应商信息
            if (supplier_ids.length > 0) {
                const supplierSql = 'SELECT supplier_id, supplier_name FROM supplier WHERE supplier_id IN (?)';
                const [supplierData] = await this.db.query(supplierSql, [supplier_ids]);
                supplierResult = supplierData;
                console.log('查询供应商表结果:', supplierResult);
            }

            // 查询入库主表信息
            if (in_id.length > 0) {
                const inventoryInSql = 'SELECT * FROM inventoryin WHERE in_id IN (?)';
                const [inventoryInData] = await this.db.query(inventoryInSql, [in_id]);
                inventoryInResult = inventoryInData;
                console.log('查询入库主表结果:', inventoryInResult);
            }

            // 从入库主表结果中提取order_id列表
            const order_ids = [...new Set(inventoryInResult.map(item => item.order_id).filter(Boolean))];
            console.log('提取的订单ID列表:', order_ids);

            // 根据order_id查询goods_order_item表
            console.log(order_ids)
            let goodsOrderItemResult = [];
            if (order_ids.length > 0) {
                const goodsOrderItemSql = 'SELECT * FROM goods_order_item WHERE order_id IN (?)';
                const [goodsOrderItemData] = await this.db.query(goodsOrderItemSql, [order_ids]);
                goodsOrderItemResult = goodsOrderItemData;
                console.log('查询goods_order_item表结果:', goodsOrderItemResult);
            }

            // 第四步：合并所有信息到库存记录中
            const combined = inventoryResults.map(item => {
                // 查找与当前item对应的入库主表数据
                const inventoryInInfo = inventoryInResult.find(inv => inv.in_id === item.in_id) || {};
                return {
                    ...item,
                    goods: goodsResult.find(g => g.goods_id === item.goods_id) || {},
                    house: houseResult.find(h => h.house_id === item.house_id) || {},
                    area: areaResult.find(a => a.area_id === item.area_id) || {},
                    supplier: supplierResult.find(s => s.supplier_id === item.supplier_id) || {},
                    // 添加入库主表信息
                    inventoryIn: inventoryInInfo,
                    // 添加商品订单子表信息
                    goodsOrderItems: goodsOrderItemResult || []
                };
            });

            console.log('合并后的结果:', combined);
            return combined;
        } catch (error) {
            console.error('GetGoodsOrderDetail函数执行错误:', error);
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }





    // 新增入库
    async AddInventoryin(body) {
        console.log("新增入库参数:", body)
        // 解构前端传入的 inventoryDetails 数组，并确保它是数组类型
        let inventoryDetails = body.inventoryDetails;
        
        // 类型检查：如果不是数组或为null/undefined，设置为空数组
        if (!Array.isArray(inventoryDetails)) {
            console.warn("inventoryDetails不是数组类型，已转换为空数组");
            inventoryDetails = [];
        }

        // 检查inventoryDetails数组长度
        console.log("inventoryDetails数组长度:", inventoryDetails.length);

        if (inventoryDetails.length === 0) {
            throw new Error('inventoryDetails数组不能为空');
        }

        // 遍历 inventoryDetails，进一步解构 goodsInfo
        const parsedDetails = inventoryDetails.map(item => {
            // 解构出 goodsInfo 对象
            const { goodsInfo, ...rest } = item;

            // 如果 goodsInfo 是字符串形式的 "[object Object]"，尝试解析
            let parsedGoodsInfo = goodsInfo;
            if (typeof goodsInfo === 'string' && goodsInfo.includes('[object Object]')) {
                try {
                    // 尝试从字符串中提取 JSON 部分（如果有）
                    const jsonStr = goodsInfo.replace(/\[object Object\]/g, '{}');
                    parsedGoodsInfo = JSON.parse(jsonStr);
                } catch (e) {
                    console.warn('goodsInfo 解析失败，使用空对象:', e.message);
                    parsedGoodsInfo = {};
                }
            } else if (typeof goodsInfo === 'object' && goodsInfo !== null) {
                // 如果已经是对象，直接使用
                parsedGoodsInfo = goodsInfo;
            } else {
                // 其他情况，使用空对象
                parsedGoodsInfo = {};
            }

            return {
                ...rest,
                goodsInfo: parsedGoodsInfo
            };
        });

        // 时间相关变量
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');

        // 在body对象中可以直接取到的参数
        const order_id = body.order_id;
        const in_type = body.in_type;
        const deliverer_name = body.deliverer_name;
        const deliverer_phone = body.deliverer_phone || '';
        const house_uid = body.house_uid || null;
        const finance_uid = body.finance_uid || null;
        const notes = body.notes || '';
        const receiver_uid = body.receiver_uid;
        const status = body.status || 1; // 默认为1-收货中
        const in_doc_pic = body.in_doc_pic || '';
        const in_amount = body.in_amount || 0;

        // 提取商品明细列表
        const items = body.items || [];
        console.log("入库商品明细数量:", items.length);

        // 构建主入库单SQL - 移除house_id字段，保持与参数数量一致
        const mainSql = 'INSERT INTO inventoryin (code, in_date, order_id, in_type, in_amount, status, deliverer_name, deliverer_phone, receiver_uid, house_uid, finance_uid, notes, created_at, updated_at, in_doc_pic) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

        try {
            // 开启事务
            await this.db.query('START TRANSACTION');

            // 只添加一条主表记录
            // 入库编号：IN+时间戳（确保唯一性）
            const in_date_str = "IN" + currentTime.replace(/[- :]/g, '').substring(0, 13);
            const code = in_date_str;

            // 从第一个明细项或body中获取house_id（如果没有则使用默认值1）
            // const house_id = (inventoryDetails[0]?.house || inventoryDetails[0]?.house_id) || body.house_id || 1;
            
            // 计算总金额（可选，如果需要汇总所有明细的金额）
            const totalAmount = inventoryDetails.reduce((sum, detail) => {
                return sum + (detail.amount || detail.subtotal || 0);
            }, 0);
  

            // 构建主表参数
            const mainParams = [
                code,
                currentTime,
                order_id,
                in_type,
                // house_id,
                in_amount,
                status,
                deliverer_name,
                deliverer_phone,
                receiver_uid,
                house_uid,
                finance_uid,
                notes,
                currentTime,
                currentTime,
                in_doc_pic
            ];

            console.log('入库主单参数:', mainParams);

            // 插入主入库单
            const [mainResult] = await this.db.query(mainSql, mainParams);
            const in_id = mainResult.insertId;
            console.log('主入库单插入成功，in_id:', in_id);

            // 保存主记录信息
            const mainRecord = {
                in_id: in_id,
                code: code,
                // area_id: inventoryDetails[0]?.area_id || 1,
                // house_id: house_id,
                in_amount: in_amount,
                order_id: order_id,
                in_type: in_type,
                status: status,
                deliverer_name: deliverer_name,
                deliverer_phone: deliverer_phone,
                receiver_uid: receiver_uid,
                house_uid: house_uid,
                finance_uid: finance_uid,
                notes: notes,
                created_at: currentTime,
                updated_at: currentTime
            };

            // 处理商品明细 - 直接处理inventoryDetails中的每个商品项
            const itemResults = [];
            console.log('开始处理商品明细，inventoryDetails.length:', inventoryDetails.length);
            console.log('inventoryDetails:', JSON.stringify(inventoryDetails, null, 2));

            for (let i = 0; i < inventoryDetails.length; i++) {
                const detail = inventoryDetails[i];
                
                console.log(`🔍 处理第${i + 1}个商品明细:`, detail);
                console.log(`   对应的主记录in_id:`, mainRecord.in_id);
                console.log(`   即将调用AddInventoryinItem方法...`);

                // 准备商品明细参数
                const itemParams = {
                    in_id: mainRecord.in_id,
                    goods_id: detail.goods_id,
                    unit: detail.unit || detail.goodsInfo?.unit || '',
                    in_price: detail.unit_price || detail.goodsInfo?.in_price || 0,
                    in_count: detail.in_count || detail.ordered_count || 0,
                    subtotal: detail.amount || detail.subtotal || 0,
                    area_id: detail.house_area || detail.area_id || 1,
                    production_date: detail.production_date || currentTime,
                    expiration_date: detail.expiration_date || currentTime,
                    notes: detail.notes || mainRecord.notes || '',
                    supplier_id: detail.supplier_id || mainRecord.supplier_id || 1,
                    house_id: detail.house || mainRecord.house_id,
                    actual_storage: detail.in_count || detail.ordered_count || 0,
                    status: 1,
                    // 修正：total_remaining = 订单库存 - 实际入库数量
                    total_remaining: (detail.ordered_count || 0) - (detail.in_count || 0),
                    is_out: 0,
                    user_id: receiver_uid,
                    related_order: order_id,
                    inventory_log_notes: detail.notes || mainRecord.notes || ''
                };
                
                // 添加total_remaining计算的调试日志
                console.log('🔢 total_remaining 参数计算:');
                console.log('   - 订单数量 (ordered_count):', detail.ordered_count || 0);
                console.log('   - 入库数量 (in_count):', detail.in_count || 0);
                console.log('   - 计算结果 (ordered_count - in_count):', (detail.ordered_count || 0) - (detail.in_count || 0));
                
                console.log(itemParams, "📦 入库子表参数");
                console.log("⚡ 正在调用 AddInventoryinItem...");
                // 调用AddInventoryinItem处理单个商品项
                const itemResult = await this.AddInventoryinItem(itemParams);
                console.log("✅ AddInventoryinItem 执行完成");

                itemResults.push({
                    ...itemResult,
                    main_in_id: mainRecord.in_id,
                    main_code: mainRecord.code
                });
            }

            // 提交事务
            await this.db.query('COMMIT');
            // 返回完整的入库信息
            return {
                success: true,
                message: `成功创建1条入库单`,
                mainRecord: mainRecord,
                mainRecords: [mainRecord],
                itemCount: itemResults.length,
                itemResults: itemResults,
                totalMainRecords: 1
            };

        } catch (error) {
            // 发生错误时回滚事务
            await this.db.query('ROLLBACK');
            console.error('新增入库失败:', error.message);
            throw new Error(`入库操作失败: ${error.message}`);
        }
    }



    // 根据商品订单编号获取订单详情
    async GetInventoryinDetail(code) {
        // 构建SQL语句，使用占位符避免SQL注入
        const sql = 'SELECT * FROM goods_order WHERE code = ?';

        console.log("根据商品订单编号获取订单详情参数:", code);
        console.log("参数类型:", typeof code);
        console.log("SQL查询语句:", sql);

        try {
            // 创建最终返回的合并对象
            const mergedData = {};

            // 1. 查询订单信息
            const [orderResult] = await this.db.query(sql, [code]);

            // 检查结果是否为空
            console.log('订单查询结果长度:', orderResult.length);
            if (orderResult.length === 0) {
                console.log(`未找到code=${code}的订单记录`);
                return {};
            }

            // 将订单信息添加到合并对象
            const orderInfo = orderResult[0];
            mergedData.orderInfo = orderInfo;
            console.log('获取到的订单信息:', orderInfo);
            

            const orderId = orderInfo.order_id;
            console.log('从订单记录中获取的order_id:', orderId);
            
            // 2. 根据order_id查询inventoryin表（入库单表）
            const inventoryInRecords = [];
            if (orderId) {
                const [inventoryInResult] = await this.db.query(
                    'SELECT * FROM inventoryin WHERE order_id = ?',
                    [orderId]
                );

                console.log(`根据order_id=${orderId}查询到的入库单数量:`, inventoryInResult.length);
                
                // 为每个入库单查询对应的入库明细
                for (const inventoryIn of inventoryInResult) {
                    const [inventoryInItemsResult] = await this.db.query(
                        'SELECT * FROM inventoryinitem WHERE in_id = ?',
                        [inventoryIn.in_id]
                    );
                    
                    // 合并入库单和入库明细数据
                    const inventoryInWithItems = {
                        ...inventoryIn,
                        items: inventoryInItemsResult
                    };
                    
                    inventoryInRecords.push(inventoryInWithItems);
                    console.log(`入库单 ${inventoryIn.in_id} 的明细数量:`, inventoryInItemsResult.length);
                }
            }
            
            // 3. 根据order_id查询订单项（原有逻辑）
            const orderItems = [];
            if (orderId) {
                const [itemsResult] = await this.db.query(
                    'SELECT * FROM goods_order_item WHERE order_id = ?',
                    [orderId]
                );

                console.log(`根据order_id=${orderId}查询到的订单子项数量:`, itemsResult.length);

                // 4. 提取所有goods_id并查询商品信息
                const goodsIds = itemsResult.map(item => item.goods_id).filter(id => id);
                console.log('从orderItems中提取的goods_id列表:', goodsIds);

                let goodsMap = {};
                if (goodsIds.length > 0) {
                    const [goodsList] = await this.db.query(
                        `SELECT * FROM goods WHERE goods_id IN (${goodsIds.map(() => '?').join(',')})`,
                        goodsIds
                    );

                    console.log(`根据goods_id列表查询到的商品数量:`, goodsList.length);
                    // 创建商品ID到商品信息的映射，提高查找效率
                    goodsMap = goodsList.reduce((map, goods) => {
                        map[goods.goods_id] = goods;
                        return map;
                    }, {});
                }

                // 将商品数据合并到订单项中
                itemsResult.forEach(item => {
                    const mergedItem = { ...item };
                    mergedItem.goodsInfo = goodsMap[item.goods_id] || null;
                    orderItems.push(mergedItem);
                });
            }

            // 将订单项添加到合并对象
            mergedData.orderItems = orderItems;
            
            // 将入库单数据添加到合并对象
            mergedData.inventoryInRecords = inventoryInRecords;

            // 添加一些元数据
            mergedData.metadata = {
                code: code,
                hasOrder: !!orderInfo,
                itemCount: orderItems.length,
                inventoryInCount: inventoryInRecords.length
            };

            // 返回完整的合并对象
            console.log('返回的合并数据对象:', mergedData);
            return mergedData;
        } catch (error) {
            console.error('根据商品订单编号获取订单详情失败:', error.message);
            // 抛出更友好的错误信息
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }


    // 添加内容到子表
    async AddInventoryinItem(body) {
        console.log('执行AddInventoryinItem方法');
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const in_id = body.in_id || null;
        const goods_id = body.goods_id || null;
        const unit = body.unit || '';
        const in_price = body.in_price || 0;
        const in_count = body.in_count || 0;
        
        const area_id = body.area_id || 1; // 修正：默认值设为1而不是in_count
        const production_date = body.production_date || currentTime;
        const expiration_date = body.expiration_date || currentTime;
        const notes = body.notes || '';
        const status = body.status || 1;
        const actual_storage = body.actual_storage || in_count; // 修正：默认值设为in_count
        const subtotal = actual_storage * in_price;
        // 修正：total_remaining应该是订单库存减去实际入库数量
        // 如果body中没有提供total_remaining，则根据in_count和actual_storage计算
        const total_remaining = body.total_remaining !== undefined ? body.total_remaining : (in_count - actual_storage);
        
        // 添加调试日志
        console.log('📊 total_remaining 计算详情:');
        console.log('   - 订单数量 (in_count):', in_count);
        console.log('   - 实际入库 (actual_storage):', actual_storage);
        console.log('   - 传入的total_remaining:', body.total_remaining);
        console.log('   - 最终计算的total_remaining:', total_remaining);
        
        const is_out = body.is_out || 0;
        const supplier_id = body.supplier_id || null;
        const house_id = body.house_id || null;
        const user_id = body.user_id || null;
        const related_order = body.related_order || null;
        const change_time = currentTime;
        const inventory_log_notes = body.inventory_log_notes || '';
        // 验证必需参数
        if (!in_id || !goods_id) {
            throw new Error('缺少必需参数：in_id 和 goods_id 不能为空');
        }

        try {
            // 方案1：先检查是否存在相同的记录
            const checkSql = 'SELECT item_id FROM inventoryinitem WHERE in_id = ? AND goods_id = ? AND area_id = ?';
            const [checkResult] = await this.db.query(checkSql, [in_id, goods_id, area_id]);

            // 如果记录存在，执行更新操作
            if (checkResult.length > 0) {
                console.log('记录已存在，执行更新操作');
                const updateSql = 'UPDATE inventoryinitem SET unit = ?, in_price = ?, in_count = ?, subtotal = ?, production_date = ?, expiration_date = ?, notes = ?, supplier_id = ?, is_out = ?, house_id = ?, actual_storage = ?, status = ?, total_remaining = ? WHERE item_id = ?';
                const updateParams = [
                    unit,
                    in_price,
                    in_count,
                    subtotal,
                    production_date,
                    expiration_date,
                    notes,
                    supplier_id,
                    is_out,
                    house_id,
                    actual_storage,
                    status,
                    total_remaining,
                    checkResult[0].item_id
                ];
                // const [updateResult] = await this.db.query(updateSql, updateParams);
                // console.log('更新成功，详情结果:', updateResult);

                // 查询所有相同goods_id的记录
                const sameGoodsSql = 'SELECT * FROM inventoryinitem WHERE goods_id = ?';
                const [sameGoodsResult] = await this.db.query(sameGoodsSql, [goods_id]);
                console.log('获取相同goods_id的所有记录:', sameGoodsResult);

                // 计算相同goods_id记录的in_count总和
                const totalInventory = sameGoodsResult.reduce((sum, record) => sum + (parseFloat(record.in_count) || 0), 0);
                console.log('商品库存总量:', totalInventory);

                // 直接更新所有goods_id相同的inventory记录，将入库数量与current_stock相加
                const updateAllInventorySql = 'UPDATE inventory SET current_stock = current_stock + ?, updated_at = ? WHERE goods_id = ?';
                const updateResult = await this.db.query(updateAllInventorySql, [parseFloat(in_count), currentTime, goods_id]);
                console.log(`成功更新goods_id=${goods_id}的所有库存记录，影响行数:`, updateResult[0].affectedRows);
                
                // 查询更新后的库存记录以获取stock_id
                const findInventorySql = 'SELECT stock_id FROM inventory WHERE goods_id = ? LIMIT 1';
                const [existingInventory] = await this.db.query(findInventorySql, [goods_id]);
                const stockId = existingInventory.length > 0 ? existingInventory[0].stock_id : null;

                // 插入库存日志（更新操作）
                const updateLogSql = 'INSERT INTO inventory_log (stock_id, goods_id, house_id, area_id,change_type, change_quantity, balance, user_id, related_order,change_time, notes) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
                const updateLogParams = [
                    stockId,
                    goods_id,
                    house_id,
                    area_id,
                    'purchase_in',
                    in_count,
                    totalInventory,
                    user_id,
                    related_order,
                    change_time,
                    inventory_log_notes + ' (更新操作)'
                ];
                const [updateLogResult] = await this.db.query(updateLogSql, updateLogParams);
                console.log('更新操作库存日志插入成功，详情结果:', updateLogResult);

                // 查询inventory表中goods_id相同的字段
                const inventoryQuerySql = 'SELECT * FROM inventory WHERE goods_id = ?';
                const [inventoryRecords] = await this.db.query(inventoryQuerySql, [goods_id]);
                console.log('获取到的inventory表中相同goods_id的记录:', inventoryRecords);

                return {
                    message: '记录更新成功',
                    updatedRecord: { ...checkResult[0], ...updateResult },
                    allRecords: sameGoodsResult,
                    totalInventory: totalInventory,
                    inventoryRecords: inventoryRecords
                };
            }

            // 方案2：记录不存在，执行插入操作
            const insertSql = 'INSERT INTO inventoryinitem (in_id, goods_id, unit, in_price, in_count, subtotal, area_id, production_date, expiration_date, notes, supplier_id, is_out, house_id, actual_storage, status, total_remaining) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

            const insertParams = [
                in_id,
                goods_id,
                unit,
                in_price,
                in_count,
                subtotal,
                area_id,
                production_date,
                expiration_date,
                notes,
                supplier_id,
                is_out,
                house_id,
                actual_storage,
                status,
                total_remaining
            ];

            console.log('准备执行SQL插入:', insertSql);
            console.log('插入参数:', insertParams);
            const [insertResult] = await this.db.query(insertSql, insertParams);
            console.log('插入成功，详情结果:', insertResult);

            // 查询刚刚插入的记录以返回完整信息
            const lastInsertSql = 'SELECT * FROM inventoryinitem WHERE item_id = ?';
            const [lastInsertResult] = await this.db.query(lastInsertSql, [insertResult.insertId]);

            // 查询所有相同goods_id的记录
            const sameGoodsSql = 'SELECT * FROM inventoryinitem WHERE goods_id = ?';
            const [sameGoodsResult] = await this.db.query(sameGoodsSql, [goods_id]);
            console.log('获取相同goods_id的所有记录:', sameGoodsResult);

            // 计算相同goods_id记录的in_count总和
            const totalInventory = sameGoodsResult.reduce((sum, record) => sum + (parseFloat(record.in_count) || 0), 0);
            console.log('商品库存总量:', totalInventory);

            // 在执行完上述操作之后向库存日志表插入一条记录
            // 直接更新所有goods_id相同的inventory记录，将入库数量与current_stock相加
            const updateAllInventorySql = 'UPDATE inventory SET current_stock = current_stock + ?, updated_at = ? WHERE goods_id = ?';
            const updateResult = await this.db.query(updateAllInventorySql, [parseFloat(in_count), currentTime, goods_id]);
            console.log(`成功更新goods_id=${goods_id}的所有库存记录，影响行数:`, updateResult[0].affectedRows);
            
            // 查询更新后的库存记录以获取stock_id
            const findInventorySql = 'SELECT stock_id FROM inventory WHERE goods_id = ? LIMIT 1';
            const [existingInventory] = await this.db.query(findInventorySql, [goods_id]);
            const stockId = existingInventory.length > 0 ? existingInventory[0].stock_id : null;

            // 插入库存日志
            const logSql = 'INSERT INTO inventory_log (stock_id, goods_id, house_id, area_id,change_type, change_quantity, balance, user_id, related_order,change_time, notes) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
            const logParams = [
                stockId, // ✅ 使用正确的stock_id
                goods_id,
                house_id,
                area_id,
                'purchase_in',
                in_count,
                totalInventory,
                user_id,
                related_order,
                change_time,
                inventory_log_notes
            ];
            const [logResult] = await this.db.query(logSql, logParams);
            console.log('库存日志插入成功，详情结果:', logResult);

                // 查询inventory表中goods_id相同的字段
                const inventoryQuerySql = 'SELECT * FROM inventory WHERE goods_id = ?';
                const [inventoryRecords] = await this.db.query(inventoryQuerySql, [goods_id]);
                console.log('获取到的inventory表中相同goods_id的记录:', inventoryRecords);

            return {
                message: '记录插入成功',
                insertedRecord: lastInsertResult[0] || { insertId: insertResult.insertId },
                allRecords: sameGoodsResult,
                totalInventory: totalInventory,
                inventoryRecords: inventoryRecords
            };
        } catch (error) {
            console.error('新增或更新入库详情失败:', error.message);

            // 特别处理唯一约束错误
            if (error.message.includes('Duplicate entry')) {
                // 方案3：如果发生唯一约束冲突，尝试自动生成新的area_id
                console.log('发生唯一约束冲突，尝试自动生成新的area_id');

                // 获取当前最大的area_id值并加1
                const maxAreaIdSql = 'SELECT MAX(area_id) as max_id FROM inventoryinitem WHERE in_id = ? AND goods_id = ?';
                const [maxResult] = await this.db.query(maxAreaIdSql, [in_id, goods_id]);
                const newAreaId = (maxResult[0].max_id || 0) + 1;

                console.log(`生成新的area_id: ${newAreaId}，重新尝试插入`);

                // 使用新的area_id重新尝试插入
                const retrySql = 'INSERT INTO inventoryinitem (in_id, goods_id, unit, in_price, in_count, subtotal, area_id, production_date, expiration_date, notes, supplier_id, is_out, house_id, actual_storage, status, total_remaining) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

                const retryParams = [
                    in_id,
                    goods_id,
                    unit,
                    in_price,
                    in_count,
                    subtotal,
                    newAreaId,
                    production_date,
                    expiration_date,
                    notes,
                    supplier_id,
                    is_out,
                    house_id,
                    actual_storage,
                    status,
                    total_remaining
                ];

                const [retryResult] = await this.db.query(retrySql, retryParams);
                console.log('使用新area_id插入成功，详情结果:', retryResult);

                // 查询所有相同goods_id的记录
                const sameGoodsSql = 'SELECT * FROM inventoryinitem WHERE goods_id = ?';
                const [sameGoodsResult] = await this.db.query(sameGoodsSql, [goods_id]);
                console.log('获取相同goods_id的所有记录:', sameGoodsResult);

                // 计算相同goods_id记录的in_count总和
                const totalInventory = sameGoodsResult.reduce((sum, record) => sum + (parseFloat(record.in_count) || 0), 0);
                console.log('商品库存总量:', totalInventory);

                // 直接更新所有goods_id相同的inventory记录，将入库数量与current_stock相加
                const updateAllInventorySql = 'UPDATE inventory SET current_stock = current_stock + ?, updated_at = ? WHERE goods_id = ?';
                const updateResult = await this.db.query(updateAllInventorySql, [parseFloat(in_count), currentTime, goods_id]);
                console.log(`成功更新goods_id=${goods_id}的所有库存记录，影响行数:`, updateResult[0].affectedRows);
                
                // 查询更新后的库存记录以获取stock_id
                const findInventorySql = 'SELECT stock_id FROM inventory WHERE goods_id = ? LIMIT 1';
                const [existingInventory] = await this.db.query(findInventorySql, [goods_id]);
                const stockId = existingInventory.length > 0 ? existingInventory[0].stock_id : null;

                // 插入库存日志（重试操作）
                const retryLogSql = 'INSERT INTO inventory_log (stock_id, goods_id, house_id, area_id,change_type, change_quantity, balance, user_id, related_order,change_time, notes) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
                const retryLogParams = [
                    stockId,
                    goods_id,
                    house_id,
                    newAreaId, // 使用新的area_id
                    'purchase_in',
                    in_count,
                    totalInventory,
                    user_id,
                    related_order,
                    change_time,
                    inventory_log_notes + ' (重试操作)'
                ];
                const [retryLogResult] = await this.db.query(retryLogSql, retryLogParams);
                console.log('重试操作库存日志插入成功，详情结果:', retryLogResult);

                // 查询刚刚重新插入的记录
                const lastRetrySql = 'SELECT * FROM inventoryinitem WHERE item_id = ?';
                const [lastRetryResult] = await this.db.query(lastRetrySql, [retryResult.insertId]);

                // 查询inventory表中goods_id相同的字段
                const inventoryQuerySql = 'SELECT * FROM inventory WHERE goods_id = ?';
                const [inventoryRecords] = await this.db.query(inventoryQuerySql, [goods_id]);
                console.log('获取到的inventory表中相同goods_id的记录:', inventoryRecords);

                return {
                    message: `唯一约束冲突，已使用新的area_id(${newAreaId})插入`,
                    insertedRecord: lastRetryResult[0] || { insertId: retryResult.insertId },
                    allRecords: sameGoodsResult,
                    totalInventory: totalInventory,
                    inventoryRecords: inventoryRecords
                };
            }

            // 抛出其他错误
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }



    async UpdateInventoryin(body, id) {
        const u_id = parseInt(id) || null;
        // 初始化SQL基础部分和参数数组
        let sql = 'UPDATE inventoryin SET ';
        const queryParams = [];
        const setClauses = [];

        // 动态添加需要更新的字段（只处理body中存在的参数）
        if (body.supplier_id !== undefined) {
            setClauses.push('supplier_id = ?');
            queryParams.push(body.supplier_id);
        }
        if (body.order_id !== undefined) {
            setClauses.push('order_id = ?');
            queryParams.push(body.order_id);
        }
        if (body.in_type !== undefined) {
            setClauses.push('in_type = ?');
            queryParams.push(body.in_type);
        }
        if (body.house_id !== undefined) {
            setClauses.push('house_id = ?');
            queryParams.push(body.house_id);
        }
        if (body.in_amount !== undefined) {
            setClauses.push('in_amount = ?');
            queryParams.push(body.in_amount);
        }
        if (body.status !== undefined) {
            setClauses.push('status = ?');
            queryParams.push(body.status);
        }
        if (body.deliverer_name !== undefined) {
            setClauses.push('deliverer_name = ?');
            queryParams.push(body.deliverer_name);
        }
        if (body.deliverer_phone !== undefined) {
            setClauses.push('deliverer_phone = ?');
            queryParams.push(body.deliverer_phone);
        }
        if (body.receiver_uid !== undefined) {
            setClauses.push('receiver_uid = ?');
            queryParams.push(body.receiver_uid);
        }
        if (body.house_uid !== undefined) {
            setClauses.push('house_uid = ?');
            queryParams.push(body.house_uid);
        }
        if (body.finance_uid !== undefined) {
            setClauses.push('finance_uid = ?');
            queryParams.push(body.finance_uid);
        }
        if (body.notes !== undefined) {
            setClauses.push('notes = ?');
            queryParams.push(body.notes);
        }
        if (body.in_doc_pic !== undefined) {  // 原查询中有该字段，补充处理
            setClauses.push('in_doc_pic = ?');
            queryParams.push(body.in_doc_pic);
        }

        // 强制更新时间字段（如果body中没有传入updated_at，则使用当前时间）
        const currentTime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        setClauses.push('updated_at = ?');
        queryParams.push(body.updated_at || currentTime);

        // 拼接SET子句和WHERE条件
        sql += setClauses.join(', ') + ' WHERE in_id = ?';
        queryParams.push(u_id);  // 添加WHERE条件的参数

        try {
            const [result] = await this.db.query(sql, queryParams);
            console.log('更新入库成功，结果:', result);
            return result;
        } catch (error) {
            console.error('更新入库失败:', error.message);
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }
    // 删除入库(软删除)
    async DeleteInventoryin(id) {
        // 提取参数并设置默认值，确保所有字段都有合法值
        const u_id = parseInt(id) || null;
        // 构建SQL语句，使用占位符避免SQL注入
        const sql = 'UPDATE inventoryin SET status=9 WHERE in_id=?';
        console.log(sql, id);
        const reqbody = [
            u_id
        ];
        console.log("删除入库参数:", reqbody);
        try {
            console.log('准备执行SQL:', sql);
            console.log('参数:', reqbody);
            const [result] = await this.db.query(sql, reqbody);
            console.log('删除成功，结果:', result);
            return result;
        } catch (error) {
            console.error('删除入库失败:', error.message);
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }
    // 查询商品订单详情
    async GetGoodsOrder(id) {
        // 提取参数并设置默认值，确保所有字段都有合法值
        const u_id = parseInt(id) || null;
        // 构建SQL语句，使用占位符避免SQL注入
        const sql = 'SELECT order_id,code,plan_id,order_date,total_amount,status,created_uid,notes,created_at,updated_at,updated_uid FROM goods_order WHERE order_id=?';
        console.log(sql, id);
        const reqbody = [
            u_id
        ];
        console.log("查询入库参数:", reqbody);
        try {
            console.log('准备执行SQL:', sql);
            console.log('参数:', reqbody);
            const [result] = await this.db.query(sql, reqbody);
            console.log('查询成功，结果:', result);
            return result;
        } catch (error) {
            console.error('查询入库失败:', error.message);
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }
    // 根据用户ID来获取用户名
    async GetUserName(user_id) {
        // 提取参数并设置默认值，确保所有字段都有合法值
        const u_id = parseInt(user_id) || null;
        // 构建SQL语句，使用占位符避免SQL注入
        const sql = 'SELECT user_name FROM user WHERE user_id=?';
        console.log(sql, user_id);
        const reqbody = [
            u_id
        ];
        console.log("根据用户ID来获取用户名参数:", reqbody);
        try {
            console.log('准备执行SQL:', sql);
            console.log('参数:', reqbody);
            const [result] = await this.db.query(sql, reqbody);
            console.log('查询成功，结果:', result);
            return result;
        } catch (error) {
            console.error('根据用户ID来获取用户名失败:', error.message);
            throw new Error(`数据库操作失败: ${error.message}`);
        }
    }

}



module.exports = InventoryinModel;